import pymysql
from pymysql.cursors import DictCursor
import logging

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

class DatabaseManager:
    def __init__(self, config):
        self.config = config
        self.connection = None
        self.connect()
        self.init_database()
        
    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.config.get('Database', 'host'),
                user=self.config.get('Database', 'user'),
                password=self.config.get('Database', 'password'),
                database=self.config.get('Database', 'database'),
                charset='utf8mb4',
                cursorclass=DictCursor
            )
            logger.info('数据库连接成功')
            return True
        except Exception as e:
            logger.error(f'数据库连接失败: {str(e)}')
            self.connection = None
            return False
        
    @staticmethod
    def test_connection(config):
        """测试数据库连接"""
        try:
            connection = pymysql.connect(
                host=config['host'],
                user=config['user'],
                password=config['password'],
                database=config['database'],
                charset='utf8mb4'
            )
            connection.close()
            return True
        except Exception as e:
            logger.error(f'测试连接失败: {str(e)}')
            return False
        
    def update_config(self, config):
        """更新配置并重新连接"""
        self.config = config
        return self.connect()
        
    def execute_query(self, sql, params=None):
        """执行查询并返回结果"""
        if not self.connection:
            if not self.connect():
                return None
        
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params or ())
                result = cursor.fetchall()
                return result
        except Exception as e:
            logger.error(f'查询执行失败: {str(e)}')
            self.connection.rollback()
            return None
        
    def execute_update(self, sql, params=None):
        """执行更新操作(INSERT/UPDATE/DELETE)"""
        if not self.connection:
            if not self.connect():
                return False
        
        try:
            with self.connection.cursor() as cursor:
                affected_rows = cursor.execute(sql, params or ())
                self.connection.commit()
                return affected_rows > 0
        except Exception as e:
            logger.error(f'更新执行失败: {str(e)}')
            self.connection.rollback()
            return False
        
    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
            logger.info('数据库连接已关闭')
        
    def __del__(self):
        self.close()
        
    def init_database(self):
        """初始化数据库表结构"""
        # 创建票据表
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS tickets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            ticket_number VARCHAR(50) NOT NULL UNIQUE,
            ticket_data TEXT NOT NULL,
            entry_state TINYINT DEFAULT 0,
            entry_time DATETIME NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
        
        return self.execute_update(create_table_sql)